<?php
class Bbs{  //定义类名，名称为“Bbs”
    private $conn = 0; //定义一个私有变量，数据库连接
    function __construct(){  //构造函数
        try{
            $this -> conn = new PDO("mysql:host=localhost;dbname=bbs","root","root");
            $this -> conn -> exec('set names utf8');
            $this -> conn -> setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
        }
        catch (PDOException $e){
            die('数据库连接失败'.$e->getMessage());
        }
    }
    function sqlQuery($sql){
        try{
            $result = $this->conn->query($sql);
            $a['rowCount'] = $result->rowCount();
            $a['data'] = $result->fetchAll(PDO::FETCH_ASSOC);
            $a['code'] = 200;
        }
        catch (PDOException $e){
            $a['code'] = -1;
            $a['msg'] = '数据库操作出错'.$e->getMessage();
        }
        return $a;
    }
    function anyQuery($table,$col,$value){
        try{
            $sql = "select * from $table where $col = ? order by id desc";
            $stm = $this->conn->prepare($sql);
            $stm->execute(array($value));
            $a['rowCount'] = $stm->rowCount();
            $a['data'] = $stm->fetchAll(PDO::FETCH_ASSOC);
            $a['code'] = 200;
        }
        catch (PDOException $e){
            $a['code'] = -1;
            $a['msg'] = '数据库操作出错';
        }
        return $a;
    }
    function update($table,$replace,$con,$value = "",$col = ""){
        //replace=0，表示更新点击次数，否则表示直接用新值替换
        try{
            if($replace){
                $sql = "update $table set $col = ? where $con";
                $stm = $this->conn->prepare($sql);
                $stm->execute(array($value));
                $rowCount = $stm->rowCount();
            }
            else{
                $sql = "update $table set hits = hits +1 where $con";
                $this->conn->exec($sql);
            }
            $a['code'] = 200;
        }
        catch (PDOException $e){
            $a['code'] = -1;
            $a['msg'] = '数据库操作出错';
        }
        return $a;
    }
    function __destruct(){

    }
    function postReply($data,$userID){
        //$sql = "insert into subtopic (userid, contents, ip, maintopicid, quoteid, posttime) VALUES ('$userID','".$data['contents']."','".$this->getIp()."','".$data['maintopicid']."','"
        $sql = "insert into subtopic (userid, contents, ip, maintopicid, quoteid, posttime) VALUES (?,?,?,?,?,?)";
        try{
            $stm = $this->conn->prepare($sql);
            $stm ->execute(array($userID,$data['contents'],$this->getIp(),$data['maintopicid'],$data['quoteid']?:NULL,date("Y-m-d H:i:s")));
            $a['code'] = 200;
        }catch (PDOException $e){
            $a['code'] = -1;
            $a['sql'] = $sql;
            $a['msg'] = '写入数据库出错'.$e->getMessage();
        }
        return $a;
    }
    function checkUserName($username){
        //检查指定用户名是否已经使用过了
        $sql = "select id from userinfo where username = ?";
        try{
            $stm = $this->conn->prepare($sql);
            $stm -> execute(array($username));
            $num = $stm -> rowCount(); //返回查询结果中的记录数
            if($num){
                $a['code'] = -1;
                $a['msg'] = '此用户名已经使用过了';
            }
            else{
                $a['code'] = 200;
            }
            return $a;
        }
        catch (PDOException $e){
            $a['code'] = -1;
            $a['msg'] = '数据库查询出错'.$e->getMessage();
            return $a;
        }

    }
    function singup($data){
        //$sql = "insert into userinfo (username, pw, nickname, email, birthday, postdate, pic) VALUES ('".$data['username']."','".md5($data['pw'])."','".$data['username']."','".$data['email']."','".$data['birthday']."','".date("Y-m-d H:i:s")."','".$data['pic']."')";
        $sql = "insert into userinfo (username, pw, nickname, email, birthday, postdate, pic) VALUES (?,?,?,?,?,?,?)";
        try {
            $stm = $this->conn->prepare($sql); //执行预处理，防止SQL注入
            $stm -> execute(array($data['username'],md5($data['pw']),$data['username'],$data['email'],$data['birthday'],date("Y-m-d H:i:s"),$data['pic']));//用数组的方式提供参数
            $a['code'] = 200;
        }
        catch (PDOException $e){
            $a['code'] = -1;
            $a['msg'] = '用户注册失败'.$e->getMessage();
        }
        return $a;
    }
    function checkLogin($username){
        $sql = "select id,username,admin,pic,postdate,nickname from userinfo where username = '$username'";
        $result = $this->conn->query($sql);
        if($result->rowCount()){
            $a['code'] = 200;
            $a['data'] = $result->fetch(PDO::FETCH_ASSOC);
        }
        else{
            $a['code'] = -1;
            $a['msg'] = '用户登录失败';
        }
        return $a;
    }
    function login($username,$pw){
        $sql = "select id,username,admin,pic,postdate,nickname from userinfo where username = '$username' and pw = '$pw'";
        $result = $this->conn->query($sql);
        if($result->rowCount()){
            $a['code'] = 200;
            $a['data'] = $result->fetch(PDO::FETCH_ASSOC);
        }
        else{
            $a['code'] = -1;
            $a['msg'] = '用户登录失败';
        }
        return $a;
    }
    function postContent($data,$userID){
        $sql = "insert into maintopic (title, contents, userid, posttime, hits, ip) VALUES (?,?,?,?,?,?)";
        try{
            $stm = $this->conn->prepare($sql);
            $stm->execute(array($data['title'],$data['contents'],$userID,date("Y-m-d H:i:s"),0,$this->getIp()));
            $a['code'] = 200;
        }
        catch (PDOException $e){
            $a['cdoe'] = -1;
            $a['msg'] = '插入数据失败.';
        }
        return $a;
    }
    function getIp()
    {
        if ($_SERVER["HTTP_CLIENT_IP"] && strcasecmp($_SERVER["HTTP_CLIENT_IP"], "unknown")) {
            $ip = $_SERVER["HTTP_CLIENT_IP"];
        } else {
            if ($_SERVER["HTTP_X_FORWARDED_FOR"] && strcasecmp($_SERVER["HTTP_X_FORWARDED_FOR"], "unknown")) {
                $ip = $_SERVER["HTTP_X_FORWARDED_FOR"];
            } else {
                if ($_SERVER["REMOTE_ADDR"] && strcasecmp($_SERVER["REMOTE_ADDR"], "unknown")) {
                    $ip = $_SERVER["REMOTE_ADDR"];
                } else {
                    if (isset ($_SERVER['REMOTE_ADDR']) && $_SERVER['REMOTE_ADDR'] && strcasecmp($_SERVER['REMOTE_ADDR'],
                            "unknown")
                    ) {
                        $ip = $_SERVER['REMOTE_ADDR'];
                    } else {
                        $ip = "unknown";
                    }
                }
            }
        }
        return ($ip);
    }
    function getData($data){
        $start = ($data['page'] - 1)*$data['perPage'];
        if($data['isSearch']){
            //说明是搜索，只显示搜索关键词所对应的结果
            $sql = "SELECT
	M.id,
	title,
	M.nickname AS MainNickName,
	posttime,
	istop,
CASE
	WHEN replyNum IS NULL THEN
	0 ELSE replyNum 
	END AS replyNum,
	hits,
CASE
	WHEN S.lastreplyposttime IS NULL THEN
		posttime ELSE S.lastreplyposttime 
	END AS lastreplyposttime,
CASE
	WHEN istop = 1 THEN
		NOW( ) 
	END AS NEWTIME,
CASE
	WHEN S.nickname IS NULL THEN
		'暂无回复' ELSE S.nickname 
	END AS SubNickName 
FROM
	(
	SELECT
		Main.id,
		title,
		posttime,
		hits,
		U.nickname,
		istop 
	FROM
		maintopic Main,
		userinfo U 
	WHERE
		U.id = Main.userid and Main.title like '%{$data['keyWords']}%'
	) M
	LEFT JOIN (
	SELECT
		S.posttime lastreplyposttime,
		U.nickname,
		maintopicid,
		replyNum,
		S.id 
	FROM
		(
		SELECT
			A.id,
			posttime,
			maintopicid,
			userid,
			replyNum 
		FROM
			subtopic A,
			( SELECT max( id ) id, COUNT( 1 ) replyNum FROM subtopic GROUP BY maintopicid ) B 
		WHERE
			A.id = B.id 
		) S,
		userinfo U 
	WHERE
		U.id = S.userid 
	ORDER BY
		S.id DESC 
	) S ON S.maintopicid = M.id 
ORDER BY
	NEWTIME DESC,
	lastreplyposttime DESC,
M.id DESC limit $start,
".$data['perPage'];
        }
        else{
            //说明是直接进入首页，显示全部结果
            $sql = "SELECT
	M.id,
	title,
	M.nickname AS MainNickName,
	posttime,
	istop,
CASE
	WHEN replyNum IS NULL THEN
	0 ELSE replyNum 
	END AS replyNum,
	hits,
CASE
	WHEN S.lastreplyposttime IS NULL THEN
		posttime ELSE S.lastreplyposttime 
	END AS lastreplyposttime,
CASE
	WHEN istop = 1 THEN
		NOW( ) 
	END AS NEWTIME,
CASE
	WHEN S.nickname IS NULL THEN
		'暂无回复' ELSE S.nickname 
	END AS SubNickName 
FROM
	(
	SELECT
		Main.id,
		title,
		posttime,
		hits,
		U.nickname,
		istop 
	FROM
		maintopic Main,
		userinfo U 
	WHERE
		U.id = Main.userid 
	) M
	LEFT JOIN (
	SELECT
		S.posttime lastreplyposttime,
		U.nickname,
		maintopicid,
		replyNum,
		S.id 
	FROM
		(
		SELECT
			A.id,
			posttime,
			maintopicid,
			userid,
			replyNum 
		FROM
			subtopic A,
			( SELECT max( id ) id, COUNT( 1 ) replyNum FROM subtopic GROUP BY maintopicid ) B 
		WHERE
			A.id = B.id 
		) S,
		userinfo U 
	WHERE
		U.id = S.userid 
	ORDER BY
		S.id DESC 
	) S ON S.maintopicid = M.id 
ORDER BY
	NEWTIME DESC,
	lastreplyposttime DESC,
M.id DESC limit $start,
".$data['perPage'];
        }
        try{
            $result = $this->conn->query($sql);
            $a['code'] = 200;
            $a['data'] = array();
            if($data['isSearch']){
                //说明是搜索，则高亮关键词
                while ($info = $result->fetch(PDO::FETCH_ASSOC)){
                    $info['title'] = preg_replace("/{$data['keyWords']}/","<font color='red'>".$data['keyWords']."</font>",$info['title']);
                    $a['data'][] = $info;
                }
            }
           else{
               $a['data'] = $result->fetchAll(PDO::FETCH_ASSOC);
           }
        }
        catch (PDOException $e){
            $a['code'] = -1;
            $a['msg'] = '数据库查询出错';
        }
        return $a;
    }
}
